/*
 * Created on 13-May-2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package wstrader.task.trade.fixation;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;

import pc.sql.value.*;

import pc.system.Record;
import pc.system.Tasks;
import pc.util.Utils;
import wstrader.task.GenReport;
import wstrader.task.trade.TradeTask;

/**
 * @author NANSA
 *
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class Contract extends TradeTask {

    public Contract(Tasks owner) {
        super(owner);
    }

    public long doTask() {
        long FixRet;
        long Ret;
        String table_name = "contract_price_fixation";
        int task_id = this.getRequest().getIntValue("task_id");

        long Pcnt_Cnt_Id = this.getRequest().getLongValue("contracts");
        long focused_id = this.getRequest().getLongValue("id");
        int FixType = this.getRequest().getIntValue("fixation_type");
        String Type = this.getRequest().getStringValue("type");
        if (Pcnt_Cnt_Id == 0) {
            Pcnt_Cnt_Id = this.getRequest().getLongValue("fetch_id");
        }

        switch (task_id) {

            case 1: // Update

                if (focused_id > 0) {
                    FixRet = doUpdate(table_name, focused_id);

                    float sumValue = getSum("tons_fixed", "contract_id", "contract_price_fixation", Pcnt_Cnt_Id);
                    //float sumDiff = (this.getRequest().getFloatValue("tottons"))-sumValue;
                    //if (sumDiff < 10) sumValue = this.getRequest().getFloatValue("tottons");
                    Ret = setFieldValue(Pcnt_Cnt_Id, "fixed_tons", " " + sumValue, "contracts");
                    Ret = setCalculateFieldValue(Pcnt_Cnt_Id, "unfixed_tons", "tons", "fixed_tons", "-", "contracts");

                    float sum_tons_fixed = getSum("tons_fixed", "contract_id", "contract_price_fixation", Pcnt_Cnt_Id);
                    Ret = setFieldValue(Pcnt_Cnt_Id, "fixed_tons", " " + sum_tons_fixed, "contracts");
                    float sum_hedge_price = getSum("hedge_price * tons_fixed", "contract_id", "contract_price_fixation", Pcnt_Cnt_Id);
                    float hedge_price = 0;
                    if (sum_tons_fixed > 0) {
                        hedge_price = (sum_hedge_price / sum_tons_fixed);
                    }
                    float sum_price = getSum("price_foreign * tons_fixed", "contract_id", "contract_price_fixation", Pcnt_Cnt_Id);
                    float price = 0;
                    if (sum_tons_fixed > 0) {
                        price = (sum_price / sum_tons_fixed);
                    }
                    Ret = setFieldValue(Pcnt_Cnt_Id, "hedge_price", " " + hedge_price, "contracts");
                    Ret = setFieldValue(Pcnt_Cnt_Id, "contract_price_foreign", " " + price, "contracts");

                    updatedPosRptField(Pcnt_Cnt_Id);

                    if (FixType > 0) {
                        updateFuturesContractRecord(FixRet, Type);
                    }
                }
                break;
            case 2: // Delete
                System.out.println("focused_id:=" + focused_id);
                if (focused_id > 0) {
                    FixRet = doDelete(table_name, focused_id);

                    float sumValue = getSum("tons_fixed", "contract_id", "contract_price_fixation", Pcnt_Cnt_Id);
                    Ret = setFieldValue(Pcnt_Cnt_Id, "fixed_tons", " " + sumValue, "contracts");
                    Ret = setCalculateFieldValue(Pcnt_Cnt_Id, "unfixed_tons", "tons", "fixed_tons", "-", "contracts");

                    float sum_tons_fixed = getSum("tons_fixed", "contract_id", "contract_price_fixation", Pcnt_Cnt_Id);
                    Ret = setFieldValue(Pcnt_Cnt_Id, "fixed_tons", " " + sum_tons_fixed, "contracts");
                    float sum_hedge_price = getSum("hedge_price * tons_fixed", "contract_id", "contract_price_fixation", Pcnt_Cnt_Id);
                    float hedge_price = 0;
                    if (sum_tons_fixed > 0) {
                        hedge_price = (sum_hedge_price / sum_tons_fixed);
                    }
                    float sum_price = getSum("price_foreign * tons_fixed", "contract_id", "contract_price_fixation", Pcnt_Cnt_Id);
                    float price = 0;
                    if (sum_tons_fixed > 0) {
                        price = (sum_price / sum_tons_fixed);
                    }
                    Ret = setFieldValue(Pcnt_Cnt_Id, "hedge_price", " " + hedge_price, "contracts");
                    Ret = setFieldValue(Pcnt_Cnt_Id, "contract_price_foreign", " " + price, "contracts");

                    updatedPosRptField(Pcnt_Cnt_Id);

                    deleteFuturesContractRecord(focused_id);
                }
                break;
            case 3: //update rollover
                if (Pcnt_Cnt_Id > 0) {
                    int RollingId = getRequest().getIntValue("roll_id");
                    if (RollingId > 0) {
                        updateRollContract(Pcnt_Cnt_Id);
                    }
                }
                break;
            case 4: //update rollover
                if ((getUser().getComanyName().toUpperCase()).compareTo("MERCAFE VIETNAM") == 0) {
                    getResponse().setReport(new PrintMercafeAmendedContract(this).generate());
                }
                break;
            case 5: //Report
                //if (Type.compareTo("P")==0) getResponse().setReport(new PrintPurFixContract(this).generate());
                //if (Type.compareTo("S")==0) getResponse().setReport(new PrintSalFixContract(this).generate());
                getResponse().setReport(new PrintSalFixContract(this).generate());
                break;

            default: //View only
                break;
        }
        this.setFocusedID(Pcnt_Cnt_Id);

        return Pcnt_Cnt_Id;
    }

    public ResultSet fetchUnfixedContractList() {
        int BuyerSellerId = getRequest().getIntValue("buyer_seller");
        String TerminalMarket = getRequest().getStringValue("terminal_market");
        java.sql.Date TerMth = getRequest().getValueDate("ter_mth");
        String Type = getRequest().getStringValue("cnt_type");
        int status = getRequest().getIntValue("status");

        if (TerminalMarket.compareTo("") == 0) {
            TerminalMarket = "0";
        }
        if (Type.compareTo("") == 0) {
            Type = "0";
        }
        String Sql = "Select * from contracts where "
                + "(supplier_buyer_id = ? OR ?) "
                + "and (terminal_market = ? OR ?) "
                + "and (terminal_month = ? OR ?) "
                + "and (type = ? OR ?) "
                + "and (status = ? OR ?) "
                + "and (contract_price_type = ?) order by contracts.id desc";

        Vector vp = new Vector();

        vp.addElement(new IntValue(BuyerSellerId));
        vp.addElement(new BooleanValue(BuyerSellerId == 0));

        vp.addElement(new StringValue(TerminalMarket));
        vp.addElement(new BooleanValue(TerminalMarket.compareTo("0") == 0));

        vp.addElement(new DateValue(TerMth));
        vp.addElement(new BooleanValue(TerMth.compareTo(new java.sql.Date(0)) == 0));

        vp.addElement(new StringValue(Type));
        vp.addElement(new BooleanValue(Type.compareTo("0") == 0));
        
        vp.addElement(new IntValue(status));
        vp.addElement(new BooleanValue(status == 0));

        vp.addElement(new StringValue("U"));

        return getSQLCommand().executeQuery(Sql, vp);
    }

    //kiendn added 14-12-2012 start.
    public ResultSet getCtSupplierBuyer() {
        String sql = "SELECT DISTINCT cm.id,cm.name"
                + " FROM contracts ct "
                + "LEFT JOIN company_master cm ON ct.supplier_buyer_id = cm.id "
                + "WHERE contract_price_type = 'U'";
        return getSQLCommand().executeQuery(sql);
    }
    //kiendn added 14-12-2012 end.

    public ResultSet fetchFixedList() {
        int PcntCntNo = getRequest().getIntValue("contracts");
        if (PcntCntNo == 0) {
            PcntCntNo = this.getRequest().getIntValue("fetch_id");
        }
        String Sql = "Select * from contract_price_fixation where contract_id = " + PcntCntNo + " order by id";
        return getSQLCommand().executeQuery(Sql);
    }

    public ResultSet fetchRollList() {
        int PcntCntNo = getRequest().getIntValue("contracts");
        if (PcntCntNo == 0) {
            PcntCntNo = this.getRequest().getIntValue("fetch_id");
        }
        String Sql = "Select * from contract_roll_over where contract_id = " + PcntCntNo + " order by id";
        return getSQLCommand().executeQuery(Sql);
    }

    public String getNew_CntFixRef() {
        String sql =
                "SELECT MAX(ref_number) as ref_number FROM contract_price_fixation ";
        Record rc = new Record(this.getSQLCommand().executeQuery(sql));
        String ref = (!rc.isEmpty()) ? rc.getString("ref_number") : "CF 00-00000";
        rc.close();
        return "CF" + " " + this.getNextRefNumber(ref);
    }

    private void updateFuturesContractRecord(long id, String CType) {
        String RefNumber = getRequest().getStringValue("fut_ref_no");
        java.sql.Date TransactionDate = Utils.strToDate(getRequest().getStringValue("fixation_date"));
        String FType = "";
        double Tons = 0;
        int Lots = 0;
        long RecExist = 0;
        if (CType.compareTo("S") == 0) {
            Tons = getRequest().getDoubleValue("tons_fixed");
            Lots = getRequest().getIntValue("lots_fixed");
            RecExist = findID("futures_contract", "fixation_id", "bot_sold", id, "B");
            FType = "B";
        } else {
            Tons = (getRequest().getDoubleValue("tons_fixed"));
            Lots = (getRequest().getIntValue("lots_fixed"));
            RecExist = findID("futures_contract", "fixation_id", "bot_sold", id, "S");
            FType = "S";
        }

        float HedgePrice = getRequest().getFloatValue("hedge_price");
        float Differential = getRequest().getFloatValue("differential");
        float Price = getRequest().getFloatValue("price_foreign");
        long FixationId = id;
        java.sql.Date TerminalMth = Utils.strToDate(getRequest().getStringValue("terminal_month"));
        String TerminalMarket = getRequest().getStringValue("terminal_market");
        int BrokerId = getRequest().getIntValue("fixation_type");
        int OrdType = 6;
        double i = 17.01;
        if (TerminalMarket.compareTo("LIFFE") == 0) {
            Tons = Lots * 5;
        } else {
            Tons = Lots * i;
        }
        String SqlStr = "";
        if (RecExist == 0) {
            SqlStr =
                    "insert futures_contract "
                    + "SET ref_number = ?, "
                    + "transaction_date = ?, "
                    + "bot_sold = ?, "
                    + "tons = ?, "
                    + "lots = ?, "
                    + "hedge_price = ?, "
                    + "differential = ?, "
                    + "price = ?, "
                    + "fixation_id = ?, "
                    + "terminal_month = ?, "
                    + "terminal_market = ?, "
                    + "broker_id = ?, "
                    + "order_type = ?, "
                    + "quality_id = ?";

            Vector vp = new Vector();
            vp.addElement(new StringValue(RefNumber));
            vp.addElement(new DateValue(TransactionDate));
            vp.addElement(new StringValue(FType));
            vp.addElement(new DoubleValue(Tons));
            vp.addElement(new FloatValue(Lots));
            vp.addElement(new FloatValue(HedgePrice));
            vp.addElement(new FloatValue(Differential));
            vp.addElement(new FloatValue(Price));
            vp.addElement(new LongValue(FixationId));
            vp.addElement(new DateValue(TerminalMth));
            vp.addElement(new StringValue(TerminalMarket));
            vp.addElement(new IntValue(BrokerId));
            vp.addElement(new IntValue(OrdType));
            if (TerminalMarket.compareTo("LIFFE") == 0) {
                vp.addElement(new IntValue(2));
            } else {
                vp.addElement(new IntValue(1));
            }
            int ret = getSQLCommand().executeUpdate(SqlStr, vp);
        } else {
            System.out.println("Update FC");
            SqlStr =
                    "update futures_contract "
                    + "SET ref_number = ?, "
                    + "transaction_date = ?, "
                    + "bot_sold = ?, "
                    + "tons = ?, "
                    + "lots = ?, "
                    + "hedge_price = ?, "
                    + "differential = ?, "
                    + "price = ?, "
                    + "fixation_id = ?, "
                    + "terminal_month = ?, "
                    + "terminal_market = ?, "
                    + "quality_id = ?, "
                    + "order_type = ?, "
                    + "broker_id = ? "
                    + "where fixation_id = ?";

            Vector vp = new Vector();
            vp.addElement(new StringValue(RefNumber));
            vp.addElement(new DateValue(TransactionDate));
            vp.addElement(new StringValue(FType));
            vp.addElement(new DoubleValue(Tons));
            vp.addElement(new FloatValue(Lots));
            vp.addElement(new FloatValue(HedgePrice));
            vp.addElement(new FloatValue(Differential));
            vp.addElement(new FloatValue(Price));
            vp.addElement(new LongValue(FixationId));
            vp.addElement(new DateValue(TerminalMth));
            vp.addElement(new StringValue(TerminalMarket));
            if (TerminalMarket.compareTo("LIFFE") == 0) {
                vp.addElement(new IntValue(2));
            } else {
                vp.addElement(new IntValue(1));
            }
            vp.addElement(new IntValue(OrdType));
            vp.addElement(new IntValue(BrokerId));
            vp.addElement(new LongValue(id));
            int ret = getSQLCommand().executeUpdate(SqlStr, vp);
        }


    }

    private void deleteFuturesContractRecord(long id) {
        System.out.println("delete from futures_contract where fixation_id = " + id);
        getSQLCommand().executeUpdate("delete from futures_contract where fixation_id = " + id);
    }

    private void updateRollContract(long id) {
        int RollId = getRequest().getIntValue("roll_id");
        java.sql.Date RollDt = Utils.strToDate(getRequest().getStringValue("rolling_date"));
        java.sql.Date RollTerMth = Utils.strToDate(getRequest().getStringValue("roll_terminal_month"));
        float LotsRolled = getRequest().getFloatValue("lots_rolled");
        float TonsRolled = getRequest().getFloatValue("tons_rolled");
        float NewDiff = getRequest().getFloatValue("new_diff");

        java.sql.Date TerMth = Utils.strToDate(getRequest().getStringValue("ter_month"));
        float Diff = getRequest().getFloatValue("cnt_differential");

        long RecExist = findID("contract_roll_over", "id", RollId);
        if (RecExist == 0) {
            String SqlStr = "";
            SqlStr =
                    "insert contract_roll_over "
                    + "SET rolling_date = ?, "
                    + "contract_id = ?, "
                    + "tons_rolled = ?, "
                    + "lots_rolled = ?, "
                    + "old_diff = ?, "
                    + "new_diff = ?, "
                    + "old_terminal_month = ?, "
                    + "new_terminal_month = ?";

            Vector vp = new Vector();
            vp.addElement(new DateValue(RollDt));
            vp.addElement(new LongValue(id));
            vp.addElement(new FloatValue(TonsRolled));
            vp.addElement(new FloatValue(LotsRolled));
            vp.addElement(new FloatValue(Diff));
            vp.addElement(new FloatValue(NewDiff));
            vp.addElement(new DateValue(TerMth));
            vp.addElement(new DateValue(RollTerMth));
            int ret = getSQLCommand().executeUpdate(SqlStr, vp);

            SqlStr = "Update contracts set terminal_month = ?, "
                    + "differential = ? "
                    + "where id = ?";
            vp = new Vector();
            vp.addElement(new DateValue(RollTerMth));
            vp.addElement(new FloatValue(NewDiff));
            vp.addElement(new LongValue(id));
            ret = getSQLCommand().executeUpdate(SqlStr, vp);
        } else {
            String SqlStr = "";
            SqlStr =
                    "update contract_roll_over "
                    + "SET rolling_date = ?, "
                    + "contract_id = ?, "
                    + "tons_rolled = ?, "
                    + "lots_rolled = ?, "
                    + "old_diff = ?, "
                    + "new_diff = ?, "
                    + "old_terminal_month = ?, "
                    + "new_terminal_month = ? "
                    + "where id = ?";

            Vector vp = new Vector();
            vp.addElement(new DateValue(RollDt));
            vp.addElement(new LongValue(id));
            vp.addElement(new FloatValue(TonsRolled));
            vp.addElement(new FloatValue(LotsRolled));
            vp.addElement(new FloatValue(Diff));
            vp.addElement(new FloatValue(NewDiff));
            vp.addElement(new DateValue(TerMth));
            vp.addElement(new DateValue(RollTerMth));
            vp.addElement(new LongValue(RollId));
            int ret = getSQLCommand().executeUpdate(SqlStr, vp);

            long LatestId = getLatestID("contract_roll_over", RollId);

            if (LatestId == RollId) {
                SqlStr = "Update contracts set terminal_month = ?, "
                        + "differential = ? "
                        + "where id = ?";
                vp = new Vector();
                vp.addElement(new DateValue(RollTerMth));
                vp.addElement(new FloatValue(NewDiff));
                vp.addElement(new LongValue(id));
                ret = getSQLCommand().executeUpdate(SqlStr, vp);
            }
        }
    }
}

class PrintSalFixContract extends GenReport {

    private Contract sales = null;

    public PrintSalFixContract(Tasks owner) {
        super(owner, "SalesCntFixation.xls");
        this.sales = (Contract) owner;
    }

    public String generate() {
        long PcntId = sales.getRequest().getLongValue("contracts");
        ResultSet Rs = null;
        Rs = getSQLCommand().executeQuery("select * from contracts where id = " + PcntId);

        try {
            Rs.beforeFirst();
            Rs.next();
            String Quality = sales.getFieldValueForId("quality_master", "name", Rs.getInt("quality_id"));

            this.set_CellValue(4, 7, Rs.getString("ref_number"));

            this.set_CellValue(4, 9, Utils.dateToStr(Rs.getDate("contract_date")));

            this.set_CellValue(4, 11, sales.getFieldValueForId("company_master", "name", Rs.getInt("supplier_buyer_id")));

            this.set_CellValue(4, 13, Utils.dateToStr(Rs.getDate("terminal_month")).substring(3));

            this.set_CellValue(4, 15, Utils.numberToStr(Rs.getFloat("differential"), 2));

            this.set_CellValue(4, 17, "US $ " + Utils.numberToStr(Rs.getFloat("contract_price_foreign"), 2));

            this.set_CellValue(4, 19, Utils.numberToStr(Rs.getFloat("tons"), 3) + " Tons.");

            this.set_CellValue(4, 21, Utils.numberToStr(Rs.getFloat("fixed_tons"), 3) + " Tons.");

            this.set_CellValue(4, 23, Utils.numberToStr(Rs.getFloat("unfixed_tons"), 3) + " Tons.");

            long Lot = 0;

            if (Quality.compareTo("Robusta") == 0) {
                Lot = Math.round(Rs.getFloat("fixed_tons") / 5);
            } else {
                Lot = Math.round(Rs.getFloat("fixed_tons") / 17.01);
            }
            this.set_CellValue(4, 25, Lot);

            if (Quality.compareTo("Robusta") == 0) {
                Lot = Math.round(Rs.getFloat("unfixed_tons") / 10);
            } else {
                Lot = Math.round(Rs.getFloat("unfixed_tons") / 17.01);
            }
            this.set_CellValue(4, 27, Lot);
            String CntType = Rs.getString("type");
            Rs.close();

            Rs = getSQLCommand().executeQuery("select * from contract_price_fixation where contract_id = " + PcntId + " Order by fixation_date");
            Rs.beforeFirst();

            int Loop = 30;
            HSSFRow TR = this.getRow((int) 30);
            HSSFCell TC = TR.getCell((short) 0);
            HSSFRow R = this.getRow((int) 30);
            HSSFCell C = R.getCell((short) 0);
            while (Rs.next()) {
                if (Loop == 30) {
                    this.set_CellValue(1, Loop, Utils.dateToStr(Rs.getDate("fixation_date")));
                    this.set_CellValue(2, Loop, Utils.numberToStr(Rs.getFloat("lots_fixed"), 0));
                    this.set_CellValue(3, Loop, " ");
                    this.set_CellValue(4, Loop, Utils.numberToStr(Rs.getFloat("tons_fixed"), 3));
                    this.set_CellValue(5, Loop, Utils.numberToStr(Rs.getFloat("hedge_price"), 2));
                    this.set_CellValue(6, Loop, Utils.numberToStr(Rs.getFloat("price_foreign"), 2));
                    this.set_CellValue(7, Loop, Utils.dateToStr(Rs.getDate("terminal_month")).substring(3));
                } else {
                    this.set_CellValue(1, Loop, Utils.dateToStr(Rs.getDate("fixation_date")));
                    R = this.getRow((int) Loop);
                    TC = TR.getCell((short) 1);
                    C = R.getCell((short) 1);
                    C.setCellStyle(TC.getCellStyle());

                    this.set_CellValue(2, Loop, Utils.numberToStr(Rs.getFloat("lots_fixed"), 0));
                    R = this.getRow((int) Loop);
                    TC = TR.getCell((short) 2);
                    C = R.getCell((short) 2);
                    C.setCellStyle(TC.getCellStyle());

                    this.set_CellValue(3, Loop, " ");
                    R = this.getRow((int) Loop);
                    TC = TR.getCell((short) 3);
                    C = R.getCell((short) 3);
                    C.setCellStyle(TC.getCellStyle());

                    this.set_CellValue(4, Loop, Utils.numberToStr(Rs.getFloat("tons_fixed"), 3));
                    R = this.getRow((int) Loop);
                    TC = TR.getCell((short) 4);
                    C = R.getCell((short) 4);
                    C.setCellStyle(TC.getCellStyle());

                    this.set_CellValue(5, Loop, Utils.numberToStr(Rs.getFloat("hedge_price"), 2));
                    R = this.getRow((int) Loop);
                    TC = TR.getCell((short) 5);
                    C = R.getCell((short) 5);
                    C.setCellStyle(TC.getCellStyle());

                    this.set_CellValue(6, Loop, Utils.numberToStr(Rs.getFloat("price_foreign"), 2));
                    R = this.getRow((int) Loop);
                    TC = TR.getCell((short) 6);
                    C = R.getCell((short) 6);
                    C.setCellStyle(TC.getCellStyle());

                    this.set_CellValue(7, Loop, Utils.dateToStr(Rs.getDate("terminal_month")).substring(3));
                    R = this.getRow((int) Loop);
                    TC = TR.getCell((short) 7);
                    C = R.getCell((short) 7);
                    C.setCellStyle(TC.getCellStyle());
                }
                Loop = Loop + 1;
            }
            Loop = Loop + 4;

            this.set_CellValue(0, Loop, "Accepted");

            Loop = Loop + 4;

            this.set_CellValue(0, Loop, "Seller : ______________________");
            this.set_CellValue(5, Loop, "Buyer : ______________________");

            Loop = Loop + 1;
            if ((sales.getUser().getComanyName().toUpperCase()).compareTo("MERCON COFFEE CORP.") == 0) {
                if (CntType.compareTo("P") == 0) {
                    this.set_CellValue(5, Loop, "Mercon Coffee Corporation");
                }
                if (CntType.compareTo("S") == 0) {
                    this.set_CellValue(0, Loop, "Mercon Coffee Corporation");
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return export();
    }
}

class PrintMercafePriceConfirmation extends GenReport {

    private Contract purchase = null;

    public PrintMercafePriceConfirmation(Tasks owner) {
        super(owner, "Mercafe_PriceConfirmation.xls");
        this.purchase = (Contract) owner;
    }

    public String generate() {
        long PcntId = purchase.getRequest().getLongValue("contracts");
        ResultSet Rs = null;
        Rs = getSQLCommand().executeQuery("select * from contracts where id = " + PcntId);

        try {
            Rs.beforeFirst();
            Rs.next();
            String Quality = purchase.getFieldValueForId("quality_master", "name", Rs.getInt("quality_id"));
            String Grade = purchase.getFieldValueForId("grade_master", "name", Rs.getInt("grade_id"));

            this.set_CellValue(0, 5, Utils.today());

            this.set_CellValue(0, 7, purchase.getFieldValueForId("company_master", "name", Rs.getInt("supplier_buyer_id")));
            this.set_CellValue(0, 8, purchase.getFieldValueForId("company_master", "address", Rs.getInt("supplier_buyer_id")));

            this.set_CellValue(5, 16, Rs.getString("ref_number"));

            this.set_CellValue(5, 18, Utils.numberToStr(Rs.getFloat("no_of_lots"), 0, false) + " Lots (" + Utils.numberToStr(Rs.getFloat("no_of_bags"), 0, false) + " Bags). " + Quality + " " + Grade);

            this.set_CellValue(0, 21, "We have priced " + Utils.numberToStr(Rs.getFloat("no_of_lots"), 0, false) + " Lots as follows.");

            this.set_CellValue(5, 24, Utils.numberToStr(Rs.getFloat("hedge_price"), 2, false));

            this.set_CellValue(5, 26, Utils.numberToStr(Rs.getFloat("differential"), 2, false));

            this.set_CellValue(5, 28, Utils.numberToStr(Rs.getFloat("contract_price_foreign"), 2, false));

            //this.getCell(5,32).setCellFormula("=F28-F30");

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return export();
    }
}

class PrintVinacofPriceConfirmation extends GenReport {

    private Contract purchase = null;

    public PrintVinacofPriceConfirmation(Tasks owner) {
        super(owner, "Vinacof Confirmation Contract.xls");
        this.purchase = (Contract) owner;
    }

    public String generate() {
        long PcntId = purchase.getRequest().getLongValue("contracts");
        ResultSet Rs = null;
        Rs = getSQLCommand().executeQuery("select * from contracts where id = " + PcntId);

        try {
            Rs.beforeFirst();
            Rs.next();
            String Quality = purchase.getFieldValueForId("quality_master", "name", Rs.getInt("quality_id"));
            String Grade = purchase.getFieldValueForId("grade_master", "name", Rs.getInt("grade_id"));

            this.set_CellValue(2, 11, Utils.today());

            this.set_CellValue(2, 12, purchase.getFieldValueForId("company_master", "name", Rs.getInt("supplier_buyer_id")));

            this.set_CellValue(2, 14, Rs.getString("ref_number"));

            this.set_CellValue(2, 16, Utils.numberToStr(Rs.getFloat("tons"), 3, false));

            this.set_CellValue(2, 17, Quality + " - " + Grade);

            float Price = (Rs.getFloat("contract_price_foreign") / 1000);
            this.set_CellValue(2, 18, Utils.numberToStr(Price, 2, false));

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return export();
    }
}

class PrintMercafeAmendedContract extends GenReport {

    private Contract purchase = null;

    public PrintMercafeAmendedContract(Tasks owner) {
        super(owner, "Mercafe_Amended_Contract.xls");
        this.purchase = (Contract) owner;
    }

    public String generate() {
        long PcntId = purchase.getRequest().getLongValue("contracts");
        long RollId = purchase.getRequest().getLongValue("roll_id");
        ResultSet Rs = null;
        String Sql = "SELECT  contract_roll_over.id,contract_roll_over.rolling_date, "
                + "contract_roll_over.contract_id,contract_roll_over.tons_rolled, "
                + "contract_roll_over.lots_rolled,contract_roll_over.old_diff, "
                + "contract_roll_over.new_diff,contract_roll_over.old_terminal_month, "
                + "contract_roll_over.new_terminal_month,contracts.contract_date, "
                + "contracts.unfixed_tons,contracts.contract_no, "
                + "contracts.supplier_buyer_id,contracts.price_fix_option,contracts.terminal_market "
                + "FROM contracts "
                + "INNER JOIN contract_roll_over ON (contracts.id=contract_roll_over.contract_id) "
                + "AND contract_roll_over.id = " + RollId;
        Rs = getSQLCommand().executeQuery(Sql);

        try {
            Rs.beforeFirst();
            Rs.next();

            this.set_CellValue(0, 12, Rs.getDate("rolling_date"));
            this.set_CellValue(7, 12, Rs.getString("contract_no"));

            this.set_CellValue(0, 15, purchase.getFieldValueForId("company_master", "name", Rs.getInt("supplier_buyer_id")));
            //this.set_CellValue(0,16,purchase.getFieldValueForId("company_master","address",Rs.getInt("supplier_buyer_id")));

            String OptStr = "";
            if (Rs.getString("price_fix_option").compareTo("B") == 0) {
                OptStr = "At Buyer Call.";
            } else {
                OptStr = "At Seller Call.";
            }

            float DiffVal = Rs.getFloat("new_diff");
            String DiffStr = "";
            if (DiffVal > 0) {
                DiffStr = "Plus";
            } else {
                DiffStr = "Minus";
            }

            String Price = "";
            Price = "To be fixed at " + OptStr + " against "
                    + Rs.getString("terminal_market") + " "
                    + Utils.dateToStr(Rs.getDate("new_terminal_month")).substring(3)
                    + " at " + DiffStr
                    + " " + Utils.numberToStr(Rs.getFloat("new_diff"), 0, false)
                    + " (" + Utils.numberToStr(Rs.getFloat("lots_rolled"), 0, false)
                    + ").";

            this.set_CellValue(1, 23, Price);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return export();
    }
}